Create Date Table , Measure And Calculated Column 8

1. Creating a Date Table:

Date = ADDCOLUMNS(
CALENDAR( MIN('Opportunities'[NewFormatDate]),
MAX( 'Opportunities'[NewFormatDate])
),
"Year", YEAR( [Date] ),
"Quarter Number", INT ( FORMAT ( [Date], "q") ),
"Quarter", "Q" & INT ( FORMAT ( [Date], "q") ),
"Month Number", MONTH ( [Date] ),
"Month", FORMAT ( [Date], "mmm" ),
"Week Day Number", WEEKDAY ( [Date] ),
"Week Day", FORMAT ( [Date], "dddd" ),
"Year Month Number", YEAR ( [Date] ) *100 + MONTH ( [Date] ),
"Year Month", FORMAT ( [Date], "mmm" ) & " " & YEAR ( [Date] ),
"Year Quarter Number", YEAR ( [Date] ) * 100 + INT ( FORMAT ( [Date], "q") ),
"Year Quarter", "Q" & FORMAT ( [Date], "q") & "-" & YEAR ( [Date] )
)


Follow the GIF to create the date table





Connect the DateTable to the opportunities Table with the date column 

And the  SalesRep Table to Rep table using Rep column












 You will follow below Screen shots to Create your measures

create on opportunity Table

  1. .Deal won =
CALCULATE (
COUNT ( 'Opportunities'[Statusid]),
FILTER (Opportunities,Opportunities[Statusid] = 5 ))
2 . WinCount = COUNT(Opportunities[Statusid])


3. Win Rate = DIVIDE([.Deal won],Opportunities[.WinCount])


4. .New Win Rate = IF(ISBLANK([Win Rate]),0,[Win Rate])


5. YoY% =
VAR __PREV_YEAR = CALCULATE([Win Rate], DATEADD('Date'[Date], -1, YEAR))
RETURN
     DIVIDE([Win Rate] - __PREV_YEAR, __PREV_YEAR)


6. YOY = SWITCH(TRUE(),[YoY%]>0,UNICHAR(9650),[YoY%] <0,UNICHAR(9660)) & ROUND ( [YoY%], 2) * 100 &"%"


7. .KPIPerformanceIndicatorColor = SWITCH(TRUE(),[YoY%]>0,"#1AAB40",[YoY%] <0,"#8B0000",[YoY%]=0, "#3a6c9b")


8. .Deal Lost Rev = CALCULATE (SUM( Opportunities[Revenue] ), FILTER (Opportunities,Opportunities[Statusid]=10))


9. .Deal won Rev = CALCULATE (SUM( Opportunities[Revenue] ), FILTER (Opportunities,Opportunities[Statusid]=5))


10. .TOTal Reve = SUM(Opportunities[Revenue])


11. .% Loss Revenue = DIVIDE( [.Deal Lost Rev], [.TOTal Reve], 0 )


12. .% won Revenue = DIVIDE( [.Deal won Rev], [.TOTal Reve], 0 )

The Gif to create measures







 Calculated Column

.Closed Revenue = if(Opportunities[Statusid]= 5,Opportunities[Revenue], BLANK())


.New Revenue = IF(ISBLANK(Opportunities[.Closed Revenue]),(0),Opportunities[.Closed Revenue])

.Lost Revenue = IF(Opportunities[Statusid] = 10, Opportunities[Revenue], 0)





For more information about the date table, please see the YouTube below.

https://www.youtube.com/watch?v=-li7sxUxEqA

Create Date Table , Measure And Calculated Column 8

1. Creating a Date Table:

Date = ADDCOLUMNS(
CALENDAR( MIN('Opportunities'[NewFormatDate]),
MAX( 'Opportunities'[NewFormatDate])
),
"Year", YEAR( [Date] ),
"Quarter Number", INT ( FORMAT ( [Date], "q") ),
"Quarter", "Q" & INT ( FORMAT ( [Date], "q") ),
"Month Number", MONTH ( [Date] ),
"Month", FORMAT ( [Date], "mmm" ),
"Week Day Number", WEEKDAY ( [Date] ),
"Week Day", FORMAT ( [Date], "dddd" ),
"Year Month Number", YEAR ( [Date] ) *100 + MONTH ( [Date] ),
"Year Month", FORMAT ( [Date], "mmm" ) & " " & YEAR ( [Date] ),
"Year Quarter Number", YEAR ( [Date] ) * 100 + INT ( FORMAT ( [Date], "q") ),
"Year Quarter", "Q" & FORMAT ( [Date], "q") & "-" & YEAR ( [Date] )
)


Follow the GIF to create the date table





Connect the DateTable to the opportunities Table with the date column 

And the  SalesRep Table to Rep table using Rep column












 You will follow below Screen shots to Create your measures

create on opportunity Table

  1. .Deal won =
CALCULATE (
COUNT ( 'Opportunities'[Statusid]),
FILTER (Opportunities,Opportunities[Statusid] = 5 ))
2 . WinCount = COUNT(Opportunities[Statusid])


3. Win Rate = DIVIDE([.Deal won],Opportunities[.WinCount])


4. .New Win Rate = IF(ISBLANK([Win Rate]),0,[Win Rate])


5. YoY% =
VAR __PREV_YEAR = CALCULATE([Win Rate], DATEADD('Date'[Date], -1, YEAR))
RETURN
     DIVIDE([Win Rate] - __PREV_YEAR, __PREV_YEAR)


6. YOY = SWITCH(TRUE(),[YoY%]>0,UNICHAR(9650),[YoY%] <0,UNICHAR(9660)) & ROUND ( [YoY%], 2) * 100 &"%"


7. .KPIPerformanceIndicatorColor = SWITCH(TRUE(),[YoY%]>0,"#1AAB40",[YoY%] <0,"#8B0000",[YoY%]=0, "#3a6c9b")


8. .Deal Lost Rev = CALCULATE (SUM( Opportunities[Revenue] ), FILTER (Opportunities,Opportunities[Statusid]=10))


9. .Deal won Rev = CALCULATE (SUM( Opportunities[Revenue] ), FILTER (Opportunities,Opportunities[Statusid]=5))


10. .TOTal Reve = SUM(Opportunities[Revenue])


11. .% Loss Revenue = DIVIDE( [.Deal Lost Rev], [.TOTal Reve], 0 )


12. .% won Revenue = DIVIDE( [.Deal won Rev], [.TOTal Reve], 0 )

The Gif to create measures







 Calculated Column

.Closed Revenue = if(Opportunities[Statusid]= 5,Opportunities[Revenue], BLANK())


.New Revenue = IF(ISBLANK(Opportunities[.Closed Revenue]),(0),Opportunities[.Closed Revenue])

.Lost Revenue = IF(Opportunities[Statusid] = 10, Opportunities[Revenue], 0)





For more information about the date table, please see the YouTube below.

https://www.youtube.com/watch?v=-li7sxUxEqA